from selenium import webdriver
from selenium.webdriver.common.by import By
import time
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait
import json
from selenium.webdriver.common.action_chains import ActionChains
import os
from copy import deepcopy

# import pyautogui
# import win32api
# import win32gui
# from win32con import WM_INPUTLANGCHANGEREQUEST
import requests
from bs4 import BeautifulSoup
import zipfile


def init():
    """检查当前目录下是否存在chrome驱动，若不存在则下载最新版chrome驱动"""
    if not os.path.exists("chromedriver/chromedriver.exe"):  # 不存在chrome驱动
        # 检查当前系统类型
        # 检查chrome版本
        # 下载最新版chrome驱动
        download_newest_chrome_driver()
    else:
        print("当前目录下存在chrome驱动")


# from webdriver_manager.chrome import ChromeDriverManager

# # 安装：pip install webdriver-manager

# driver_path = ChromeDriverManager().install()

def download_newest_chrome_driver():
    # 获取下载链接
    url = "https://googlechromelabs.github.io/chrome-for-testing/#stable"
    try:
        # 发送GET请求
        response = requests.get(url)
        # 检查请求是否成功（状态码200）
        response.raise_for_status()
        # 获取网页内容（自动处理编码）
        content = response.text
    except requests.exceptions.RequestException as e:
        print(f"请求失败: {e}")
    soup = BeautifulSoup(response.text, "lxml")
    table = soup.find_all("table")[1]
    table = soup.find_all("tbody")[1]
    infos = dict()
    for tr in table.find_all("tr")[1:]:
        ths = tr.find_all("th")
        tds = tr.find_all("td")
        infos["{}-{}".format(ths[0].text.strip(), ths[1].text.strip())] = tds[
            0
        ].text.strip()
    chrome_driver_url = infos["chromedriver-win64"]
    chrome_driver_name = chrome_driver_url.split("/")[-1]
    # 下载最新版chrome驱动
    try:
        print("正在下载Chrome驱动...")
        print(chrome_driver_url)
        headers = {
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36"
        }
        response = requests.get(chrome_driver_url, headers=headers)
        response.raise_for_status()  # 检查请求是否成功
        with open(chrome_driver_name, "wb") as file:
            file.write(response.content)  # 将内容写入文件
        print("Chrome驱动下载完成")
    except requests.exceptions.RequestException as e:
        print(f"下载失败: {e}")
    # 下载完成后，解压zip文件到当前目录
    try:
        with zipfile.ZipFile(chrome_driver_name, "r") as zip_ref:
            zip_ref.extractall()
        os.remove(chrome_driver_name)
        os.rename(chrome_driver_name.replace(".zip", ""), "chromedriver")
        print("解压完成")
    except Exception as e:
        print(f"解压失败: {e}")


# 被导入时自动执行
init()


def change_language(lang="EN"):
    """
    切换语言
    :param lang: EN––English; ZH––Chinese
    :return: bool
    """
    LANG = {"ZH": 0x0804, "EN": 0x0409}
    hwnd = win32gui.GetForegroundWindow()
    language = LANG[lang]
    result = win32api.SendMessage(hwnd, WM_INPUTLANGCHANGEREQUEST, 0, language)
    if not result:
        return True


def out(xpath):
    html = driver.find_element(By.XPATH, xpath).get_attribute("outerHTML")
    with open("out.html", "w", encoding="utf8") as wf:
        wf.write(html)


def sleep():
    time.sleep(0.5)


def shortsleep():
    time.sleep(0)


def longsleep():
    time.sleep(5)


def click(xpath):
    # button = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    button = driver.find_element(By.XPATH, xpath)
    button.click()
    shortsleep()


def exist(xpath):
    try:
        driver.find_element(By.XPATH, xpath)
    except:
        return False
    else:
        return True


def write(xpath, text):
    inputTag = driver.find_element(By.XPATH, xpath)
    # inputTag = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # inputTag = driver.find_element(By.XPATH, xpath)
    inputTag.clear()
    inputTag.send_keys(text)
    shortsleep()


def get_html(xpath):
    # # item = driver.find_element(By.XPATH, xpath)
    # item = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # # return item.text
    # # return item.get_attribute('textContent')
    html = driver.find_element(By.XPATH, xpath).get_attribute("outerHTML")
    return html


def get_value(xpath):
    html = driver.find_element(By.XPATH, xpath).get_attribute("value")
    return html


def get_text(driver,xpath):
    # item = driver.find_element(By.XPATH, xpath)
    # item = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # return item.text
    # return item.get_attribute('textContent')
    text = driver.find_element(By.XPATH, xpath).text
    return text


def switch(xpath):
    iframe = WebDriverWait(driver, 10).until(
        EC.presence_of_element_located((By.XPATH, xpath))
    )
    # iframe = driver.find_element(By.XPATH, xpath)
    driver.switch_to.frame(iframe)
    shortsleep()


def exist_dt(dt_name):
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[4]/a")  ## 点击‘资源维护’
    ## 切换iframe
    switch('//*[@id="frame"]')
    ## 输入查询内容
    write("html/body/div[1]/section/div/form/div[1]/input", dt_name)
    ## 点击库表
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")
    ## 点击查询
    click("/html/body/div[1]/section/div/form/div[6]/button[1]")
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')

    def match():
        if "表中无数据存在" in x:
            return False
        for j in range(x.count("<tr")):
            if (
                get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(j + 1))
                == dt_name
            ):
                # and get_text('//*[@id="list-table"]/tbody/tr[{}]/td[3]'.format(j+1))=='市信息中心':
                return True
        return False

    return match()


def auto_dt_register(system_name, catalog, db_name, shujuku, shujubiao):
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[3]/a")  # 点击
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[3]/ul/li[1]/a")  # 点击
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 转换
    write(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[1]/div/input",
        db_name,
    )  #  填入资源名称
    sleep()
    click('//*[@id="selectFromSystemDiv"]/span')  #  点击选择系统
    sleep()
    write(
        "/html/body/div[6]/div/div/div[2]/div[1]/div/div/div[1]/div/input", system_name
    )  # 写入 系统名称
    click(
        "/html/body/div[6]/div/div/div[2]/div[1]/div/div/div[2]/button[1]"
    )  # 点击查询
    sleep()
    click(
        "/html/body/div[6]/div/div/div[2]/div[2]/div/div/div[1]/table/tbody/tr/td[1]/input"
    )  # 点击查到的内容
    click("/html/body/div[6]/div/div/div[3]/button[1]")  # 点击确定
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[3]/div/div/span/button"
    )  # 点击选择目录
    write(
        "/html/body/div[4]/div/div/div[2]/div[1]/div/input[3]", catalog
    )  # 写入目录名称
    click("/html/body/div[4]/div/div/div[2]/div[1]/div/button[1]")  # 点击查询
    sleep()
    sleep()
    while not exist('//span[text()="{}"]'.format(catalog)):
        click('//*[@id="catalogs-table_next"]')
        sleep()
    click('//span[text()="{}"]'.format(catalog))  # 点击查到的内容
    # click('//*[@id="catalogs-table"]/tbody/tr[1]/td[2]/span')#=============================================================================================================================================
    click("/html/body/div[4]/div/div/div[3]/button[1]")  # 点击确定
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[5]/div[1]/select"
    )  # 点击数据更新周期
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[5]/div[1]/select/option[6]"
    )  # 选择每半年
    write(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[12]/div/textarea",
        db_name,
    )  # 写入库表描述
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[2]/a"
    )  # 点击下一步
    sleep()
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[1]/div/div/span/button"
    )  # 点击选择数据表
    sleep()
    write(
        "/html/body/div[2]/div/div/div[2]/div/div/div[1]/input[1]", shujuku
    )  # 写入 接口库表数据库
    click("/html/body/div[2]/div/div/div[2]/div/div/div[1]/button")  # 点击查询
    sleep()
    if shujuku == "信用库":
        click(
            "/html/body/div[2]/div/div/div[2]/div/div/div[1]/div/div[1]/table/tbody/tr[2]/td[1]/input"
        )  # 选择查到的内容
    else:
        click(
            "/html/body/div[2]/div/div/div[2]/div/div/div[1]/div/div[1]/table/tbody/tr[1]/td[1]/input"
        )  # 选择查到的内容
    write(
        "/html/body/div[2]/div/div/div[2]/div/div/div[2]/input", shujubiao
    )  # 写入表名
    click("/html/body/div[2]/div/div/div[2]/div/div/div[2]/button")  # 点击查询
    sleep()
    while not exist('//input[@value="{}"]'.format(shujubiao.lower())):
        click('//*[@id="dataTableTable_next"]')
        sleep()
    click('//input[@value="{}"]'.format(shujubiao.lower()))  # 选择查到的内容
    click("/html/body/div[2]/div/div/div[3]/button[2]")  # 点击确定
    write(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[6]/div/div/input",
        "500",
    )  # 写入 500
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[4]/a"
    )  # 点击保存并提交
    driver.switch_to.default_content()  # 转到主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/a")  # 点击资源审核
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/ul/li[1]/a")  # 点击资源注册审核
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 转到frame
    write("/html/body/div[1]/section/div/form/div[1]/input", db_name)  # 写入库表名称
    # click('/html/body/div[1]/section/div/form/div[4]/select') #点击资源类型
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")  # 点击库表
    click("/html/body/div[1]/section/div/form/div[5]/button[1]")  # 点击查询
    sleep()
    click(
        "/html/body/div[1]/section/div/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a"
    )  # 点击审核
    sleep()
    click(
        "/html/body/div/section/div/div/div[2]/div[1]/form/div/div/button[1]"
    )  # 点击审核通过
    click("/html/body/div[3]/div[3]/a[1]")  # 点击确定
    sleep()
    sleep()
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[6]/a")  # 点击资源发布
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 切换frame
    write("/html/body/div[1]/section/div/form/div[1]/input", db_name)  # 输入资源名称
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")  # 点击库表
    click('//button[@id="search_resource"]')  # 点击查询
    sleep()
    click(
        "/html/body/div[1]/section/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a"
    )  # 点击发布
    driver.switch_to.default_content()  # 切换主页面
    click('//a[text()="确定"]')  # 点击确定


def login_resource():
    driver.maximize_window()
    # 打开页面
    driver.get("http://10.64.202.213/resource")
    # 填入用户名和密码
    write("/html/body/div/section/div/div[1]/form/div[1]/input", "platform_admin")
    write("/html/body/div/section/div/div[1]/form/div[2]/input", "!QAZ2wsx")
    # 手动输入验证码
    change_language()
    click("/html/body/div/section/div/div[1]/form/div[3]/div/div/input")
    longsleep()
    # 点击登录
    click("/html/body/div/section/div/div[1]/form/button")


def exist_interface(interface_name):
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
    click('//*[@id="nav-side"]/li[8]/ul/li[2]')  # 点击数据服务维护
    switch('//*[@id="frame"]')  # 切换frame
    write('//*[@id="search_resource_title_code"]', interface_name)  # 填入资源名称
    click('//*[@id="search_resource"]')  # 点击查询
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')  # 获取内容

    def match():
        if "表中无数据存在" in x:
            return False
        for i in range(1, x.count("<tr") + 1):
            # 匹配接口名
            if interface_name == get_text(
                '//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)
            ):
                return True
        return False

    return match()


def auto_interface_register(
    system_name,
    catalog_name,
    dt_name,
    table_name,
    interface_index,
    interface_name,
    interface_name_en,
):
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
    # /html/body/div[3]/div[1]/div/div[2]/ul/li[8]
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]/ul/li[1]")  # 点击数据服务注册
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 切换frame
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[1]/div/div/span/button"
    )  # 点击选择库表资源
    write("/html/body/div[6]/div/div/div[2]/div[1]/div/input", dt_name)  # 填入 dbname
    click("/html/body/div[6]/div/div/div[2]/div[1]/div/button[1]")  # 点击查询
    sleep()
    sleep()
    # click('//td[text()="{}"]/parent::tr/td[1]/input'.format(dt_name))# 点击目标库表
    # print(get_html())
    if not exist('//span[@title="{}"]/../../../td[1]/input'.format(dt_name)):
        dt_name = dt_name + "新"
        if not exist('//span[@title="{}"]/../../../td[1]/input'.format(dt_name)):
            return "库表不存在"
    click('//span[@title="{}"]/../../../td[1]/input'.format(dt_name))  # 点击目标库表
    click("/html/body/div[6]/div/div/div[3]/button[1]")  # 点击确定。
    write(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[2]/div/input",
        interface_name,
    )  # 写入 库表名+信息查询接口
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[3]/div/div/span/button"
    )  # 点击选择系统
    sleep()
    write(
        "/html/body/div[5]/div/div/div[2]/div[1]/div/div/div[1]/div/input", system_name
    )  # 写入系统名
    click(
        "/html/body/div[5]/div/div/div[2]/div[1]/div/div/div[2]/button[1]"
    )  # 点击查询
    sleep()
    click('//input[@data-name="{}"]'.format(system_name))  # 点击目标系统
    click("/html/body/div[5]/div/div/div[3]/button[1]")  # 点击确定
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[4]/div/div/span/button"
    )  # 点击选择目录
    write(
        "/html/body/div[4]/div/div/div[2]/div[1]/div/input[3]", catalog_name
    )  # 写入目录名称
    click("/html/body/div[4]/div/div/div[2]/div[1]/div/button[1]")  # 点击查询
    sleep()
    while not exist('//span[@value="{}"]'.format(catalog_name)):
        click('//*[@id="catalogs-table_next"]')  # 点击下一页
        sleep()
    click('//span[@value="{}"]'.format(catalog_name))  # 点击目标目录
    click("/html/body/div[4]/div/div/div[3]/button[1]")  # 点击确定
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[6]/div[1]/select/option[6]"
    )  # 点击每半年数据更新
    write(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[15]/div/textarea",
        interface_name,
    )  # 写入库表名+信息查询接口
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[2]/a"
    )  # 点击下一步
    sleep()
    write(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[1]/div/input",
        "/" + interface_name_en,
    )  # 写入/表名
    sleep()
    click('//*[@id="steps-uid-0-p-1"]/div/div[1]/label')  # 点击标签
    sleep()
    if exist('//*[@id="servicePath-error"]'):
        print("地址重复")
        return "地址重复"
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[2]/div/div[2]/button"
    )  # 点击添加参数
    sleep()
    if not exist('//option[@value="{}"]'.format(interface_index)):
        return "索引不存在"
    click('//option[@value="{}"]'.format(interface_index))  # 选择ID名
    write(
        "/html/body/div[2]/div/div/div[2]/div/div[5]/div/input", interface_index
    )  # 写入    TTTTTT_TID
    click("/html/body/div[2]/div/div/div[3]/button[1]")  # 点击确定
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[3]/div/div/textarea[2]"
    )  # 点击服务逻辑
    sleep()
    sleep()
    click(
        "/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div/span[1]"
    )  # 点击添加条件
    click(
        '/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[1]/select/option[@value="{}"]'.format(
            interface_index
        )
    )  # 选择项目
    click(
        '/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[2]/select/option[@value="="]'
    )  # 选择等于
    write(
        "/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[3]/input",
        interface_index,
    )
    # click('/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[3]/ul/li/a')  # 点击元素
    click("/html/body/div[3]/div/div/div[3]/button[1]")  # 点击确定
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[4]/div/div[2]/button"
    )  # 点击全选
    click(
        "/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[4]/a"
    )  # 点击保存并提交
    driver.switch_to.default_content()  # 切换主页面
    sleep()
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]/ul/li[3]")  # 点击数据服务审核
    click(
        "/html/body/div[3]/div[1]/div/div[2]/ul/li[8]/ul/li[3]/ul/li[1]"
    )  # 点击注册审核
    sleep()
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 切换frame
    # write('/html/body/div[1]/section/div/form/div[1]/input',db_name+'信息查询接口') # 写入dbname
    # click('//*[@id="search_resource"]') # 点击查询
    # click('/html/body/div[1]/section/div/form/div[5]/button[1]')# 点击查询
    # element = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, '/html/body/div[1]/section/div/form/div[5]/button[1]')))
    # element.click()
    click(
        "/html/body/div[1]/section/div/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a"
    )  # 点击审核
    click(
        "/html/body/div/section/div/div/div[2]/div[1]/form/div/div/button[1]"
    )  # 点击审核通过
    click("/html/body/div[3]/div[3]/a[1]")  # 点击确定
    return "接口已存在"


def test_login():
    # 打开页面
    driver.get("http://10.64.202.213/manage")
    # driver.execute_script("document.body.style.zoom='0.8'")
    # 填入用户名和密码
    write("/html/body/div/section/div/div[1]/form/div[1]/input", "platform_admin")
    write("/html/body/div/section/div/div[1]/form/div[2]/input", "!QAZ2wsx")
    # 手动输入验证码
    click("/html/body/div/section/div/div[1]/form/div[3]/div/div/input")
    longsleep()
    # 点击登录
    click("/html/body/div/section/div/div[1]/form/button")


def auto_test(interface_name, interface_par):
    driver.get("http://10.64.202.213/manage")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[1]/div/div[3]/div[2]/ul/li[2]")  # 点击接口注册
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 切换frame
    write(
        "/html/body/div/section/div/div/div/div[1]/form/div[1]/input", ""
    )  # 填入接口名
    write(
        "/html/body/div/section/div/div/div/div[1]/form/div[1]/input", interface_name
    )  # 填入接口名
    click("/html/body/div/section/div/div/div/div[1]/form/div[2]/button[1]")  # 点击查询
    sleep()
    x = get_html('//*[@id="service-test-table"]/tbody')
    if "表中无数据存在" in x:
        return "待测试状态中无此接口", ""
    ind = 0
    for i in range(1, x.count("<tr") + 1):
        if interface_name == get_text(
            '//*[@id="service-test-table"]/tbody/tr[{}]/td[2]'.format(i)
        ):
            ind = i
            break
    click(
        "/html/body/div/section/div/div/div/div[1]/div/div/div[1]/table/tbody/tr[{}]/td[5]/a".format(
            ind
        )
    )  # 点击测试
    click(
        "/html/body/div/section/div/div[2]/form/div[1]/div[1]/div/a"
    )  # 点击测试，此时开新标签页
    sleep()
    # 切换标签页
    n = (
        driver.window_handles
    )  # 这个时候会生成一个新窗口或新标签页的句柄，代表这个窗口的模拟driver
    driver.switch_to.window(n[-1])  # driver切换至最新生产的页面
    if exist('//div[text()=" 403 "]'):
        return "跳过", "403"
    write(
        "/html/body/section/div/form/div[4]/div/table/tbody/tr/td[2]/input",
        interface_par,
    )  # 写入测试数据
    click("/html/body/section/div/form/div[5]/div/button[1]")  # 点击发送请求
    result = get_value(
        "/html/body/section/div/form/div[6]/div/textarea"
    )  # 获取输出内容
    i = 0
    while not result:
        result = get_value("/html/body/section/div/form/div[6]/div/textarea")
        i += 1
        sleep()
    driver.close()
    driver.switch_to.window(n[0])
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 切换frame
    # 检查输出内容
    x = json.loads(result)
    if x["code"] and x["data"] and interface_par in result:
        driver.execute_script(
            "window.scrollTo(0, document.body.scrollHeight);"
        )  # 下拉到页面底部
        write(
            "/html/body/div/section/div/div[2]/form/div[1]/div[2]/div/textarea",
            result[:400],
        )
        click("/html/body/div/section/div/div[2]/form/div[2]/div/button[1]")  # 点击通过
        driver.switch_to.default_content()  # 切换主页面
        click('//div[@id="layui-layer1"]/div[3]/a[1]')  # 点击确认
        while len(x["data"]) > 1:
            del x["data"][1]
        return "测试通过", json.dumps(x)
    else:
        return "测试不通过", result


def interface_test():
    # 登录系统
    test_login()
    df = pd.read_excel("fengong1.xlsx")
    for i in range(df.shape[0]):
        if pd.isnull(df.iloc[i]["测试情况"]):
            re = auto_test(df.iloc[i]["库表"] + "信息查询接口", df.iloc[i]["测试用例"])
            if re:
                print(df.iloc[i]["库表"], "完成")
                df.iloc[i]["测试情况"] = re
                print(re)
                df.to_excel("{}.xlsx".format(i))
    # service__register('市政务大数据平台', '市资源共享服务平台', '城镇单位在岗职工平均工资年均增长速度', 'czdwzgzzpjgznjzzsd')
    # driver.quit()
    # quit()
    df.to_excel("qqqq.xlsx")


def resource():
    # driver = webdriver.Chrome()
    resource_login()
    # # 读取数据
    # df = pd.read_excel('fengong1.xlsx')
    # for i in range(df.shape[0]):
    #     print()
    #     if pd.isnull(df.iloc[i]['注册接口情况']):
    #         service_register('市政务大数据平台', '市资源共享服务平台', df.iloc[i]['库表'], df.iloc[i]['接口'])
    #         # df.iloc[i]['库表注册情况']='注册库表完成'
    #         # df.to_excel('fengong1.xlsx')
    #         print(df.iloc[i]['库表'],'完成')
    # # service__register('市政务大数据平台', '市资源共享服务平台', '城镇单位在岗职工平均工资年均增长速度', 'czdwzgzzpjgznjzzsd')
    # driver.quit()
    # quit()


def login_submit():
    driver.get("http://10.64.202.219:8081")  # 打开页面
    write("/html/body/form/div/div[2]/div[1]/div[1]/input", "ndszb_lbf")  # 填入用户名
    write("/html/body/form/div/div[2]/div[1]/div[2]/input", "lbf@2021")  # 填入密码
    # 手动输入验证码
    # click('/html/body/div/section/div/div[1]/form/div[3]/div/div/input')
    # longsleep()
    # 点击登录
    click("/html/body/form/div/div[2]/div[1]/input")


def login_bsp():
    driver.get("http://10.64.202.213/bsp")  # 打开页面
    write(
        "/html/body/div[2]/section/div[1]/div[1]/form/div[1]/input[1]", "platform_admin"
    )  # 填入用户名
    write(
        "/html/body/div[2]/section/div[1]/div[1]/form/div[2]/input", "!QAZ2wsx"
    )  # 填入密码
    # 手动输入验证码
    click("/html/body/div[2]/section/div[1]/div[1]/form/div[3]/div/div/input")
    # longsleep()
    # 点击登录
    click("/html/body/div[2]/section/div[1]/div[1]/form/button")


def auto_submit(
    guishubumen,
    interface_name,
    return_value,
    parameter_name,
    parameter_value,
    interface_address,
    biaoming,
    suoyinshuoming,
    chucanmiaoshu,
):
    """执行一条自动提交动作"""
    login_submit()
    sleep()
    sleep()
    click("/html/body/div[2]/div[1]/ul/li[7]/a")  # 点击接口管理
    switch("/html/body/div[2]/div[2]/iframe")  # 切换iframe
    click("/html/body/div[2]/div[1]/a[2]")  # 点击地市上报接口
    write("/html/body/div[2]/div[2]/input[2]", interface_name)  # 写入服务名称
    click("/html/body/div[2]/div[2]/input[3]")  # 点击搜索
    sleep()
    if exist("/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]"):  # 可以搜索到
        click("/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]")  # 点击查看详情
        sleep()
        switch('//*[@id="layui-layer-iframe1"]')
        sleep()
        click('//option[text()="{}"]'.format(guishubumen))  # 点击机构名称
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[2]/input", interface_name
        )  # 写入接口名称
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[3]/input",
            interface_address,
        )  # 写入接口地址
        click('//*[@id="typeselect"]/option[1]')  # 点击接口类型
        click(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[2]/td[2]/span/select/option[2]"
        )  # 点击请求类型get
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[3]/td/input", biaoming
        )  # 写入查询业务表
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[4]/td/textarea", interface_name
        )  # 写入接口描述
        x = json.loads(return_value)
        while len(x["data"]) > 1:
            del x["data"][1]
        while len(x["data"][0]) > 50:
            data_ = list(x["data"][0].keys())[0]
            del x["data"][0][data_]
        for k in x["data"][0].keys():
            if isinstance(x["data"][0][k], str):
                x["data"][0][k] = x["data"][0][k][:20].replace('"', "")
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[5]/td/textarea",
            json.dumps(x, ensure_ascii=False),
        )  # 更改返回值
        write(
            "/html/body/div[1]/div/div[2]/table/tbody/tr/td[2]/input", parameter_name
        )  # 写入参数名称
        write(
            "/html/body/div[1]/div/div[2]/table/tbody/tr/td[4]/input", suoyinshuoming
        )  # 写入参数描述
        write(
            "/html/body/div[1]/div/div[2]/table/tbody/tr/td[8]/input", parameter_value
        )  # 写入请求示例
        z = json.loads(return_value)
        for k in z["data"][0].keys():
            if isinstance(z["data"][0][k], str):
                z["data"][0][k] = z["data"][0][k][:20].replace('"', "")
        z = z["data"][0]
        y = json.loads(chucanmiaoshu)
        for i in range(get_html('//*[@id="outtable"]/tbody').count("<tr")):
            # click('/html/body/div[1]/div/h2[2]/a') # 点击添加输出参数
            write(
                '//*[@id="outtable"]/tbody/tr[{}]/td[2]/input'.format(i + 1),
                "par" + str(i),
            )
            write(
                '//*[@id="outtable"]/tbody/tr[{}]/td[4]/input'.format(i + 1),
                "par" + str(i),
            )
            write(
                '//*[@id="outtable"]/tbody/tr[{}]/td[6]/input'.format(i + 1),
                "par" + str(i),
            )
        driver.execute_script("window.scrollBy(0,10)")  # 下拉页面
        click('//*[@id="savebtn"]')  # 点击更新
        WebDriverWait(driver, 10).until(EC.alert_is_present())
        x = driver.switch_to.alert
        sleep()
        sleep()
        x.accept()
        sleep()
        driver.switch_to.default_content()  # 切换主页面
        click("/html/body/div[2]/div[1]/ul/li[7]/a")  # 点击接口管理
        switch("/html/body/div[2]/div[2]/iframe")  # 切换iframe
        click("/html/body/div[2]/div[1]/a[2]")  # 点击地市上报接口
        write("/html/body/div[2]/div[2]/input[2]", interface_name)  # 写入服务名称
        click("/html/body/div[2]/div[2]/input[3]")  # 点击搜索
        click("/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[2]")  # 点击删除
        sleep()
        sleep()
        sleep()
        x = driver.switch_to.alert
        sleep()
        re = x.text
        sleep()
        x.accept()
        sleep()
        sleep()
        x = driver.switch_to.alert
        sleep()
        re = x.text
        sleep()
        x.accept()
        print(re)
        if re == "删除失败！接口不存在":
            click("/html/body/div[2]/div[2]/input[4]")  # 点击服务上报
            click('//a[contains(string(), "HttpRestFul")]')  # 点击http
            sleep()
            driver.switch_to.default_content()  # 切换主页面
            switch('//*[@id="iframepage"]')
            sleep()
            iframe = driver.find_element(By.XPATH, '//*[@id="layui-layer-iframe2"]')
            driver.switch_to.frame(iframe)
            click('//option[text()="{}"]'.format(guishubumen))  # 点击机构名称
            write(
                "/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[2]/input",
                interface_name,
            )  # 写入接口名称
            write(
                "/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[3]/input",
                interface_address,
            )  # 写入接口地址
            click('//*[@id="typeselect"]/option[1]')  # 点击接口类型
            click(
                "/html/body/div[1]/div/div[1]/table/tbody/tr[2]/td[2]/span/select/option[2]"
            )  # 点击请求类型get
            write(
                "/html/body/div[1]/div/div[1]/table/tbody/tr[3]/td/input", biaoming
            )  # 写入查询业务表
            write(
                "/html/body/div[1]/div/div[1]/table/tbody/tr[4]/td/textarea",
                interface_name,
            )  # 写入接口描述
            x = json.loads(return_value)
            while len(x["data"]) > 1:
                del x["data"][1]
            while len(x["data"][0]) > 50:
                data_ = list(x["data"][0].keys())[0]
                del x["data"][0][data_]
            for k in x["data"][0].keys():
                if isinstance(x["data"][0][k], str):
                    x["data"][0][k] = x["data"][0][k][:20].replace('"', "")
            write(
                "/html/body/div[1]/div/div[1]/table/tbody/tr[5]/td/textarea",
                json.dumps(x, ensure_ascii=False),
            )  # 更改返回值
            click("/html/body/div[1]/div/h2[1]/a")  # 点击添加输入参数
            write(
                "/html/body/div[1]/div/div[2]/table/tbody/tr/td[2]/input",
                parameter_name,
            )  # 写入参数名称
            write(
                "/html/body/div[1]/div/div[2]/table/tbody/tr/td[4]/input",
                suoyinshuoming,
            )  # 写入参数描述
            write(
                "/html/body/div[1]/div/div[2]/table/tbody/tr/td[8]/input",
                parameter_value,
            )  # 写入请求示例
            z = json.loads(return_value)
            for k in z["data"][0].keys():
                if isinstance(z["data"][0][k], str):
                    z["data"][0][k] = z["data"][0][k][:20].replace('"', "")
            z = z["data"][0]
            i = 1
            y = json.loads(chucanmiaoshu)
            for k, v in z.items():
                click("/html/body/div[1]/div/h2[2]/a")  # 点击添加输出参数
                write('//*[@id="outtable"]/tbody/tr[{}]/td[2]/input'.format(i), k)
                write(
                    '//*[@id="outtable"]/tbody/tr[{}]/td[4]/input'.format(i),
                    y[k] if y[k] else k,
                )
                write('//*[@id="outtable"]/tbody/tr[{}]/td[6]/input'.format(i), v)
                i += 1
            driver.execute_script("window.scrollBy(0,10)")  # 下拉页面
            click('//*[@id="savebtn"]')  # 点击保存
            WebDriverWait(driver, 10).until(EC.alert_is_present())
            x = driver.switch_to.alert
            re = x.text
            sleep()
            x.accept()
            return re
        return auto_submit(
            guishubumen,
            interface_name,
            return_value,
            parameter_name,
            parameter_value,
            interface_address,
            biaoming,
            suoyinshuoming,
            chucanmiaoshu,
        )
    else:  # 不能搜索到则上报接口
        click("/html/body/div[2]/div[2]/input[4]")  # 点击服务上报
        click('//a[contains(string(), "HttpRestFul")]')  # 点击http
        sleep()
        driver.switch_to.default_content()  # 切换主页面
        switch('//*[@id="iframepage"]')
        sleep()
        iframe = driver.find_element(By.XPATH, '//*[@id="layui-layer-iframe2"]')
        driver.switch_to.frame(iframe)
        click('//option[text()="{}"]'.format(guishubumen))  # 点击机构名称
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[2]/input", interface_name
        )  # 写入接口名称
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[3]/input",
            interface_address,
        )  # 写入接口地址
        click('//*[@id="typeselect"]/option[1]')  # 点击接口类型
        click(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[2]/td[2]/span/select/option[2]"
        )  # 点击请求类型get
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[3]/td/input", biaoming
        )  # 写入查询业务表
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[4]/td/textarea", interface_name
        )  # 写入接口描述
        print(return_value)
        x = json.loads(return_value)
        while len(x["data"]) > 1:
            del x["data"][1]
        while len(x["data"][0]) > 50:
            data_ = list(x["data"][0].keys())[0]
            del x["data"][0][data_]
        for k in x["data"][0].keys():
            if isinstance(x["data"][0][k], str):
                x["data"][0][k] = x["data"][0][k][:20].replace('"', "")
        write(
            "/html/body/div[1]/div/div[1]/table/tbody/tr[5]/td/textarea",
            json.dumps(x, ensure_ascii=False),
        )  # 更改返回值
        click("/html/body/div[1]/div/h2[1]/a")  # 点击添加输入参数
        write(
            "/html/body/div[1]/div/div[2]/table/tbody/tr/td[2]/input", parameter_name
        )  # 写入参数名称
        write(
            "/html/body/div[1]/div/div[2]/table/tbody/tr/td[4]/input", suoyinshuoming
        )  # 写入参数描述
        write(
            "/html/body/div[1]/div/div[2]/table/tbody/tr/td[8]/input", parameter_value
        )  # 写入请求示例
        z = json.loads(return_value)
        for k in z["data"][0].keys():
            if isinstance(z["data"][0][k], str):
                z["data"][0][k] = z["data"][0][k][:20].replace('"', "")
        z = z["data"][0]
        i = 1
        y = json.loads(chucanmiaoshu)
        for k, v in z.items():
            click("/html/body/div[1]/div/h2[2]/a")  # 点击添加输出参数
            write('//*[@id="outtable"]/tbody/tr[{}]/td[2]/input'.format(i), k)
            write(
                '//*[@id="outtable"]/tbody/tr[{}]/td[4]/input'.format(i),
                y[k] if y[k] else k,
            )
            write('//*[@id="outtable"]/tbody/tr[{}]/td[6]/input'.format(i), v)
            i += 1
        driver.execute_script("window.scrollBy(0,10)")  # 下拉页面
        click('//*[@id="savebtn"]')  # 点击保存
        WebDriverWait(driver, 10).until(EC.alert_is_present())
        x = driver.switch_to.alert
        re = x.text
        sleep()
        x.accept()
        return re


def auto_unsubmit(interface_name):
    """执行一条自动提交动作"""
    login_submit()
    sleep()
    click("/html/body/div[2]/div[1]/ul/li[7]/a")  # 点击接口管理
    switch("/html/body/div[2]/div[2]/iframe")  # 切换iframe
    click("/html/body/div[2]/div[1]/a[2]")  # 点击地市上报接口
    write("/html/body/div[2]/div[2]/input[2]", interface_name)  # 写入服务名称
    click("/html/body/div[2]/div[2]/input[3]")  # 点击搜索
    sleep()
    if exist("/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]"):  # 可以搜索到
        # click('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]') # 点击查看详情
        # sleep()
        # switch('//*[@id="layui-layer-iframe1"]')
        # sleep()
        # click('//*[@id="savebtn"]') #点击更新
        # sleep()
        # sleep()
        # sleep()
        # sleep()
        # x = driver.switch_to.alert
        # sleep()
        # sleep()
        # x.accept()
        # sleep()
        # driver.switch_to.default_content() #切换主页面
        # click('/html/body/div[2]/div[1]/ul/li[7]/a')# 点击接口管理
        # switch('/html/body/div[2]/div[2]/iframe') # 切换iframe
        # click('/html/body/div[2]/div[1]/a[2]') # 点击地市上报接口
        # write('/html/body/div[2]/div[2]/input[2]', interface_name) # 写入服务名称
        # click('/html/body/div[2]/div[2]/input[3]') # 点击搜索
        click("/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[2]")  # 点击删除
        sleep()
        sleep()
        x = driver.switch_to.alert
        sleep()
        x.accept()
        sleep()


def submit():
    file_name = "1新设计接口清单接口.xlsx"
    df = pd.read_excel(file_name, converters={"appkey": str, "入参示例": str})
    for i in df.index:
        # if df.loc[i, '接口是否上报'] != '上报过程出错':
        #     continue
        if i != 77:
            continue
        print(i, df.loc[i, "接口"], end=" ")
        try:
            re = auto_submit(
                df.loc[i, "上报机构新"],
                df.loc[i, "接口"],
                df.loc[i, "代码测试输出"],
                df.loc[i, "索引"],
                df.loc[i, "入参示例"],
                df.loc[i, "接口地址"],
                df.loc[i, "表名"],
                df.loc[i, "索引说明"],
                df.loc[i, "出参字段描述"],
            )
            df.loc[i, "接口是否上报"] = re
            print(re)
        except Exception as e:
            print(e)
            df.loc[i, "接口是否上报"] = "上报过程出错"
            df.to_excel("result.xlsx")
            print("上报过程出错")
            # quit()
    df.to_excel(file_name.replace(".xlsx", "_上报结果.xlsx"))


def login_portal():
    login_resource()


def login_inquiry():
    driver.maximize_window()
    # 打开页面
    driver.get("http://10.64.202.213/inquiry/")
    # 填入用户名和密码
    write('//*[@id="i-username"]', "platform_admin")
    write('//*[@id="i-password"]', "!QAZ2wsx")
    # 点击登录
    click('//*[@id="dzzw-igp-form-login"]/button')


def auto_resource_check(interface):
    def match(text):
        if "表中无数据存在" in text:
            return 0
        for i in range(text.count("<tr")):
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[1]'.format(i+1)), catalog)
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[2]'.format(i+1)), interface)
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[3]'.format(i+1)), organization)
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[4]'.format(i+1)), '服务接口')
            if (
                get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[2]'.format(i + 1))
                == interface
                and get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[4]'.format(i + 1))
                == "服务接口"
            ):
                return i + 1
        return 0

    def get_appkey(n):
        click(
            '//*[@id="table-apply_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[3]'.format(
                n
            )
        )  # 点击查看授权
        sleep()
        sleep()
        b = get_text('//*[@id="secretInfo"]/table/tbody/tr[2]/td[2]')  # 获取appkey
        c = get_text('//*[@id="servicePath"]')  # 获取接口地址
        return b, c

    driver.get("http://10.64.202.213/portal")  # 打开页面
    change_language()
    click("/html/body/div[1]/div[1]/div/div/a[2]")  # 点击用户中心
    click('//*[@id="accordion"]/li[3]/a')  # 点击我的申请
    click('//*[@id="pMyResourceApply"]')  # 点击我的申请
    switch('//*[@id="screenIfram"]')  # 切换iframe
    click("/html/body/div[1]/section/div/section/div/header/ul/li[3]/a")  # 点击审核通过
    write('//*[@id="searchNameApply"]', interface)  # 输入查询信息，接口名称
    click('//*[@id="search_data_apply"]')  # 点击查询按钮
    sleep()
    # 精确选择筛选的按钮
    x = match(get_html('//*[@id="table-apply"]/tbody'))
    if x:
        result = get_appkey(x)
        return result[0], result[1], "资源审核已通过"
    else:
        driver.switch_to.default_content()  # 切换主页面
        click('//*[@id="pApi"]/a')  # 点击政府服务资源
        sleep()
        write('//*[@id="name_forsearch"]', interface)  # 输入搜索内容
        click('//*[@id="select-border"]/ul/li[2]')  # 点击接口
        click('//*[@id="keyword_search"]')  # 点击搜索按钮
        sleep()
        if not exist('//a[@title="{}"]'.format(interface)):
            return "", "", "接口尚未发布"
        click('//a[@title="{}"]'.format(interface))  # 点击搜索到的第一个元素
        driver.switch_to.window(driver.window_handles[-1])  # driver切换至最新生产的页面
        sleep()
        sleep()
        click("/html/body/div[3]/div/div/div[1]/div[2]/ul/li[1]/a")  # 点击资源申请
        click('//*[@id="userSystemInfo"]/div/div/span/button')  # 点击选择
        sleep()
        sleep()
        click('//*[@id="df-58"]')  # 点击数据接口选择
        sleep()
        click('//*[@id="buttonusemodel"]')  # 点击办事场景选择
        sleep()
        click('//*[@id="item-list"]/tbody/tr[2]/td[4]/input')  # 点击复选框
        click('//*[@id="selectedItem"]')  # 点击确定
        write('//*[@id="apply_basis"]', "暂无")  # 写入申请依据 暂无
        write(
            '//*[@id="form_advance"]/div[7]/div[1]/div/div/input', "50000"
        )  # 写入 50000
        write(
            '//*[@id="form_advance"]/div[7]/div[2]/div/div/input', "50000"
        )  # 写入 50000
        write(
            '//*[@id="form_advance"]/div[8]/div[1]/div/div/input', "1095"
        )  # 写入 1095
        click('//*[@id="service_usetime"]/option[2]')  # 点击全天
        click('//*[@id="pickfiles"]')  # 点击选择文件
        sleep()
        sleep()
        pyautogui.write(os.getcwd() + r"\Word.docx")
        sleep()
        sleep()
        pyautogui.press("enter")  # 敲击enter进行保存
        sleep()
        sleep()
        sleep()
        sleep()
        click('//*[@id="btn-info-botton"]')  # 点击提交申请
        sleep()
        click('//*[@id="gotoUserCenter"]')  # 点击用户中心
        click('//*[@id="accordion"]/li[2]/a')  # 点击申请审核
        click('//*[@id="pUserResourceAudit"]')  # 点击资源申请审核
        switch('//*[@id="screenIfram"]')
        click(
            '//*[@id="table-resApply_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[1]/td/a'
        )  # 点击受理
        sleep()
        sleep()
        write('//*[@id="opinion"]', "通过")  # 写入通过
        click('//*[@id="resource-accept-botton"]')  # 点击受理
        click('//*[@id="layui-layer1"]/div[3]/a[1]')  # 点击确定
        longsleep()
        driver.close()
        driver.switch_to.window(driver.window_handles[0])
        return auto_resource_check(interface)


def portal():
    login_portal()
    df = pd.read_excel("分工.xlsx")
    try:
        for i in df.index:
            if pd.isnull(df.iloc[i]["appkey"]):
                interface_code, appkey, interface_address, support_unit = auto_portal(
                    df.iloc[i]["资源提供方"],
                    df.iloc[i]["目录"],
                    df.iloc[i]["库表"] + "信息查询接口",
                )
                df.loc[i, "接口编码"] = interface_code
                df.loc[i, "appkey"] = appkey
                df.loc[i, "rest接口"] = interface_address
                df.loc[i, "技术支持单位"] = support_unit
            print("{}. {} has done!".format(i, df.iloc[i]["库表"]))
    except Exception as e:
        print(e)
    finally:
        df.to_excel("result.xlsx")


def get_data_from_database():
    # 从数据库
    conn = pymysql.connect(
        host="10.64.202.214",
        port=3306,
        user="root",
        password="inspur135?!",
        db="dsp_cascade_ningde",
        charset="utf8",
    )
    cursor = conn.cursor()
    df = pd.read_excel("全部接口.xlsx")
    for i in df.index:
        print(df.loc[i, "TABLENAME"])
        table = df.loc[i, "TABLENAME"]
        # 获取字段名
        sql = "show columns from {}".format(table)
        rows = cursor.execute(sql)
        data = cursor.fetchall()
        df.loc[i, "数据库字段"] = str(data)
        sql = "select * from {} LIMIT 1".format(table)
        rows = cursor.execute(sql)
        data = cursor.fetchall()
        df.loc[i, "数据库数据示例"] = str(data)
    cursor.close()
    conn.close()
    df.to_excel("全部接口1.xlsx")


def exist_register(catalog_name):
    """判断一个目录是否存在
    采用目录名完全匹配的方式，不匹配提供方
    """
    driver.get("http://10.64.202.213/catalog/")
    click('//*[@id="nav-side"]/li[3]')  # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[1]')  # 点击目录编制
    switch('//*[@id="frame"]')  # 切换frame
    write('//*[@id="cata_title"]', catalog_name)  # 写入目录名称
    click('//*[@id="cata_status"]/option[7]')  # 点击已发布
    click('//*[@id="search_data"]')  # 点击查询
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')  # 获取表格内容

    def match():
        if "表中无数据存在" in x:
            return False
        for i in range(1, x.count("<tr") + 1):
            # 匹配目录名
            if catalog_name == get_text(
                '//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(i)
            ):
                return True
        return False

    return match()


def auto_catalog_register(organization_name, system_name, catalog_name, ziduan):
    re = organization_name
    leixing = {
        "longtext": "长文本text",
        "tinytext": "长文本text",
        "datetime": "日期时间型T",
        "varchar": "字符串型C",
        "decimal": "浮点型F",
        "longblob": "二进制blob",
        "mediumtext": "长文本text",
        "char": "字符串型C",
        "double": "浮点型F",
        "int": "整型I",
        "timestamp": "日期时间型T",
        "blob": "二进制blob",
        "text": "长文本text",
        "date": "日期时间型T",
        "tinyint": "整型I",
        "bigint": "整型I",
        "float": "浮点型F",
    }
    driver.get("http://10.64.202.213/catalog/")
    click('//*[@id="nav-side"]/li[3]')  # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[1]')  # 点击目录编制
    switch('//*[@id="frame"]')  # 切换frame
    sleep()
    click('//*[@id="#/3070194903"]/i')  # 点击 + ，展开部门资源目录
    sleep()
    click('//*[@id="#/3070194903/307019490303"]/i')  # 点击+，展开行政部门目录
    sleep()
    sleep()
    if not exist('//a[text()="{}"]'.format(organization_name)):
        organization_name = "市信息中心"
        re = "市信息中心"
    target = driver.find_element(By.XPATH, '//a[text()="{}"]'.format(organization_name))
    sleep()
    driver.execute_script("arguments[0].scrollIntoView();", target)
    sleep()
    click('//a[text()="{}"]'.format(organization_name))  # 选择市信息中心
    sleep()
    sleep()
    target = driver.find_element(By.XPATH, '//*[@id="table-toolbar"]/div[9]/button[4]')
    sleep()
    sleep()
    driver.execute_script("arguments[0].scrollIntoView();", target)
    sleep()
    click('//*[@id="table-toolbar"]/div[9]/button[4]')  # 点击添加目录
    write('//*[@id="indiv"]/div[2]/div/input', catalog_name)  # 写入目录名
    click('//*[@id="selectFromSystemDiv"]/span/button')  # 点击选择系统
    sleep()
    write('//*[@id="from_system_search_keywords"]', system_name)  # 输入系统名
    click('//*[@id="from_system_search_data"]')  # 点击查询
    sleep()
    click('//*[@id="dataCenterTable"]/tbody/tr/td[1]/input')  # 点击选择按钮
    click('//*[@id="selectFromSystem"]/div/div/div[2]/button[1]')  # 点击确定
    click('//*[@id="catalog_shared_type"]/option[2]')  # 共享类型选择有条件共享
    write(
        '//*[@id="catalog_shared_condition"]', "经领导审批后共享"
    )  # 写入共享条件 经领导审批后共享
    click('//*[@id="catalog_open_type"]/option[3]')  # 开放类型选择 不开放
    write('//*[@id="description"]', catalog_name)  # 写入来源系统
    click('//*[@id="steps-uid-0"]/div[3]/ul/li[2]')  # 点击下一步
    sleep()
    sleep()
    # ==================================================================
    # 解析字段
    for i in ziduan.split(","):
        j = i.split(";")
        click('//*[@id="steps-uid-0-p-1"]/div/div[1]/div/a[2]')  # 点击添加
        sleep()
        write('//*[@id="name_cn"]', j[0])  # 写入信息项名称
        click(
            '//*[@id="data_format"]/option[text()="{}"]'.format(leixing[j[1]])
        )  # 点击类型
        if j[2]:
            write('//*[@id="length"]', j[2])  # 写入长度
        click('//*[@id="column_submit"]')  # 点击确定
    click('//*[@id="steps-uid-0"]/div[3]/ul/li[4]/a')  # 点击提交
    sleep()
    # 目录审核
    driver.switch_to.default_content()  # 切换主页面
    click('//*[@id="nav-side"]/li[3]')  # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[4]')  # 点击目录审核
    sleep()
    switch('//*[@id="frame"]')  # 切换frame
    sleep()
    click(
        '//*[@id="list-approve_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a'
    )  # 点击审核
    click('//*[@id="submitDIV"]/div/button[1]')  # 点击审核通过
    click('//*[@id="layui-layer1"]/div[3]/a[1]')  # 点击确定

    # 目录发布
    driver.switch_to.default_content()  # 切换主页面
    click('//*[@id="nav-side"]/li[3]')  # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[3]')  # 点击目录发布
    switch('//*[@id="frame"]')  # 切换frame
    sleep()
    click(
        '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a'
    )  # 点击发布
    click('//*[@id="submitDIV"]/div/button[1]')  # 点击发布
    click('//*[@id="layui-layer1"]/div[3]/a[1]')  # 点击确定
    state = "目录已存在"
    return re


def return_value_test(interface_address, input_par_name, input_par_value, appkey):
    import requests

    if (
        pd.isnull(interface_address)
        or pd.isnull(input_par_name)
        or pd.isnull(input_par_value)
        or pd.isnull(appkey)
    ):
        return "信息不全,无法测试", ""
    url = "{}?{}={}".format(interface_address, input_par_name, input_par_value)
    payload = {}
    headers = {"appkey": appkey}
    response = requests.request("GET", url, headers=headers, data=payload)
    x = json.loads(response.text)
    if (
        "code" in x
        and "data" in x
        and x["code"] == 200
        and x["data"]
        and input_par_value in response.text
    ):
        return "测试通过", response.text
    else:
        return "测试不通过", response.text


def catalog():
    df = pd.read_excel("全部数据.xlsx", converters={"appkey": str})
    for i in df.index:
        print(i, df.loc[i, "接口"])
        if df.loc[i, "目录是否存在"] == "目录已存在":
            continue
        if df.loc[i, "页面测试是否通过"] != "数据库填报错误":
            continue
        if pd.isnull(df.loc[i, "目录"]) or df.loc[i, "目录"] == "":
            df.loc[i, "目录是否存在"] = "目录为空"
            continue
        if pd.isnull(df.loc[i, "字段"]) or df.loc[i, "字段"] == "":
            df.loc[i, "目录是否存在"] = "字段为空"
            continue
        if pd.isnull(df.loc[i, "机构"]) or df.loc[i, "机构"] == "":
            df.loc[i, "目录是否存在"] = "机构为空"
            continue
        try:
            if not exist_register(df.loc[i, "目录"]):
                re = auto_catalog_register(
                    df.loc[i, "机构"],
                    "市政务大数据平台",
                    df.loc[i, "目录"],
                    df.loc[i, "字段"],
                )
                df.loc[i, "机构"] = re
            else:
                df.loc[i, "目录是否存在"] = "目录已存在"
        except:
            df.loc[i, "目录是否存在"] = "目录注册过程出错"
            # df.to_excel('quanbushuju.xlsx')
            continue
        df.loc[i, "目录是否存在"] = "目录已存在"
    df.to_excel("全部数据.xlsx")


def dt():
    df = pd.read_excel("接口第一版-测试未通过.xlsx", converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "页面测试是否通过"] != "库表重新注册":
            continue
        if pd.isnull(df.loc[i, "目录"]) or df.loc[i, "目录"] == "":
            df.loc[i, "库表是否存在"] = "目录为空"
            continue
        if pd.isnull(df.loc[i, "数据库名"]) or df.loc[i, "数据库名"] == "":
            df.loc[i, "库表是否存在"] = "数据库名为空"
            continue
        if pd.isnull(df.loc[i, "表名"]) or df.loc[i, "表名"] == "":
            df.loc[i, "库表是否存在"] = "表名为空"
            continue
        if pd.isnull(df.loc[i, "库表"]) or df.loc[i, "库表"] == "":
            df.loc[i, "库表是否存在"] = "库表为空"
            continue
        print(i, df.loc[i, "库表"])
        try:
            if not exist_dt(df.loc[i, "库表"]):
                auto_dt_register(
                    "市政务大数据平台",
                    df.loc[i, "目录"],
                    df.loc[i, "库表"],
                    df.loc[i, "数据库名"],
                    df.loc[i, "表名"],
                )
            else:
                df.loc[i, "库表是否存在"] = "库表已存在"

        except Exception as e:
            df.loc[i, "库表是否存在"] = "库表注册过程出错"
            print(e)
            df.to_excel("quanbushuju.xlsx")
            print("库表注册过程出错")
            quit()
            continue
        df.loc[i, "库表是否存在"] = "库表已存在"
    df.to_excel("quanbushuju.xlsx")


def interface():
    file_name = "1新设计接口清单接口正在页面测试.xlsx"
    df = pd.read_excel(file_name, converters={"appkey": str})
    for i in df.index:
        if pd.isnull(df.loc[i, "目录"]) or df.loc[i, "目录"] == "":
            df.loc[i, "接口是否存在"] = "目录为空"
            continue
        if pd.isnull(df.loc[i, "索引"]) or df.loc[i, "索引"] == "":
            df.loc[i, "接口是否存在"] = "索引为空"
            continue
        if pd.isnull(df.loc[i, "表名"]) or df.loc[i, "表名"] == "":
            df.loc[i, "接口是否存在"] = "表名为空"
            continue
        if pd.isnull(df.loc[i, "库表"]) or df.loc[i, "库表"] == "":
            df.loc[i, "接口是否存在"] = "库表为空"
            continue
        if pd.isnull(df.loc[i, "接口"]) or df.loc[i, "接口"] == "":
            df.loc[i, "接口是否存在"] = "接口为空"
            continue
        print("接口注册", i, df.loc[i, "接口"])
        try:
            if not exist_interface(df.loc[i, "接口"]):
                re = auto_interface_register(
                    "市政务大数据平台",
                    df.loc[i, "目录"],
                    df.loc[i, "库表"],
                    df.loc[i, "表名"],
                    df.loc[i, "索引"],
                    df.loc[i, "接口"],
                    df.loc[i, "接口地址英文后缀"],
                )
                print(re)
                df.loc[i, "接口是否存在"] = re
            else:
                df.loc[i, "接口是否存在"] = "接口已存在"
        except Exception as e:
            print(e)
            df.loc[i, "接口是否存在"] = "接口注册过程出错"
            df.to_excel(file_name.replace(".xlsx", "_接口注册.xlsx"))
            # quit()
    df.to_excel(file_name.replace(".xlsx", "_接口注册.xlsx"))


def unsubmit():
    df = pd.read_excel("全部数据-删上报.xlsx", converters={"appkey": str})
    for i in df.index:
        print("接口删除", i, df.loc[i, "接口"])
        if df.loc[i, "接口是否已删除"] == "接口已删除":
            continue
        try:
            auto_unsubmit(df.loc[i, "接口"])
        except:
            df.loc[i, "接口是否已删除"] = "接口删除过程出错"
            continue
        df.loc[i, "接口是否已删除"] = "接口已删除"
    df.to_excel("全部数据-删上报123.xlsx")


def web_test():
    file_name = "1新设计接口清单接口正在页面测试.xlsx"
    df = pd.read_excel(file_name, converters={"appkey": str, "入参示例": str})
    for i in df.index:
        # if df.loc[i, '页面测试是否通过'] != '库表重新注册':
        #     continue
        # if df.loc[i, '页面测试是否通过'] == '测试通过':
        #     continue
        # if df.loc[i, '页面测试是否通过'] == '等待时间过长':
        #     continue
        # if df.loc[i, '页面测试是否通过'] == '待测试状态中无此接口':
        #     continue
        if pd.isnull(df.loc[i, "入参示例"]) or df.loc[i, "入参示例"] == "":
            df.loc[i, "页面测试是否通过"] = "入参示例为空"
            continue
        if pd.isnull(df.loc[i, "接口"]) or df.loc[i, "接口"] == "":
            df.loc[i, "页面测试是否通过"] = "接口为空"
            continue
        print(i, df.loc[i, "接口"])
        try:
            re = auto_test(df.loc[i, "接口"], df.loc[i, "入参示例"])
            if re[0] == "待测试状态中无此接口":
                df.loc[i, "页面测试是否通过"] = re[0]
            else:
                df.loc[i, "页面测试是否通过"] = re[0]
                df.loc[i, "页面测试输出"] = re[1]
                print(re[0])
        except:
            df.loc[i, "页面测试是否通过"] = "页面测试过程出错"
            print("页面测试过程出错")
            df.to_excel(file_name.replace(".xlsx", "_页面测试.xlsx"))
            # quit()
    df.to_excel(file_name.replace(".xlsx", "_页面测试.xlsx"))


def check():
    file_name = "1新设计接口清单接口.xlsx"
    df = pd.read_excel(file_name, converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "资源审核是否通过"] == "资源审核已通过":
            continue
        print(i, df.loc[i, "接口"])
        if pd.isnull(df.loc[i, "接口"]) or df.loc[i, "接口"] == "":
            df.loc[i, "资源审核是否通过"] = "接口为空"
            continue
        try:
            result = auto_resource_check(df.loc[i, "接口"])
            df.loc[i, "appkey"] = result[0]
            df.loc[i, "接口地址"] = result[1]
            df.loc[i, "资源审核是否通过"] = result[2]
        except:
            df.loc[i, "资源审核是否通过"] = "审核过程出错"
            df.to_excel(file_name.replace(".xlsx", "_资源审核结果.xlsx"))
            continue
    df.to_excel(file_name.replace(".xlsx", "_资源审核结果.xlsx"))


def find_index():
    global driver
    df = pd.read_excel("全部数据.xlsx", converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "代码测试是否通过"] == "测试不通过":
            print(i, df.loc[i, "接口"])
            driver.get("http://10.64.202.213/resource")
            driver.switch_to.default_content()  # 切换主页面
            click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
            click('//*[@id="nav-side"]/li[8]/ul/li[2]/a')  # 点击数据服务维护
            switch('//*[@id="frame"]')  # 切换frame
            # click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[1]/div/div/span/button') # 点击选择库表资源
            write(
                '//*[@id="search_resource_title_code"]', df.loc[i, "接口"]
            )  # 写入资源名称
            click('//*[@id="search_resource"]')  # 点击查询
            sleep()
            click(
                '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[1]/td/a[1]'
            )  # 点击查看
            sleep()
            click('//*[@id="tab_1_1"]/div[1]/ul/li[2]/a')  # 点击数据服务信息
            df.loc[i, "索引"] = get_text(
                '//*[@id="tab_dataService_2"]/div/div[2]/div/table/tbody/tr/td[1]'
            )  # 获取索引名
    df.to_excel("quanbushuju.xlsx")


def find_index_value():
    global driver
    df = pd.read_excel("全部数据.xlsx", converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "代码测试是否通过"] == "测试不通过":
            try:
                print(i, df.loc[i, "接口"])
                host = df.loc[i, "数据库"].strip().split("/")[0]
                db = df.loc[i, "数据库"].strip().split("/")[1]
                table = df.loc[i, "表名"].strip()
                print(host, db, table)
                if host == "10.64.202.214":
                    conn = pymysql.connect(
                        host=host,
                        port=3306,
                        user="root",
                        password="inspur135?!",
                        db=db,
                        charset="utf8",
                    )
                else:
                    conn = pymysql.connect(
                        host=host,
                        port=3306,
                        user="root",
                        password="123456a?",
                        db=db,
                        charset="utf8",
                    )
                cursor = conn.cursor()
                sql = "select {} from {} LIMIT 1".format(
                    df.loc[i, "索引"].strip(), table
                )
                rows = cursor.execute(sql)
                data = cursor.fetchall()
                # df.loc[i,'数据库数据示例'] = str(data)
                print(str(data[0][0]))
                cursor.close()
                conn.close()
                df.loc[i, "入参示例"] = str(data[0][0])
            except Exception as e:
                print(e)
                continue
    df.to_excel("quanbushuju.xlsx")


def auto_huanshujuku(interface_name):
    def match():
        x = get_html('//*[@id="list-table"]/tbody')  # 获取内容
        if "表中无数据存在" in x:
            return 0
        for i in range(1, x.count("<tr") + 1):
            # 匹配接口名
            if interface_name == get_text(
                '//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)
            ):
                return i
        return 0

    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
    click('//*[@id="nav-side"]/li[8]/ul/li[2]')  # 点击数据服务维护
    switch('//*[@id="frame"]')  # 切换frame
    write('//*[@id="search_resource_title_code"]', interface_name)  # 填入资源名称
    click('//*[@id="search_resource_status"]/option[2]')  # 点击草稿
    click('//*[@id="search_resource"]')  # 点击查询
    sleep()
    n = match()
    if n:
        click(
            '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr["{}"]/td/a[2]'.format(
                n
            )
        )


def huanshujuku():
    df = pd.read_excel("全部数据.xlsx", converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "页面测试是否通过"] == "数据库填报错误":
            auto_huanshujuku(df.loc[i, "接口"])


def get_chucan(jiekoudizhi, suoyin, rucanshili, appkey):
    url = "{}?{}={}".format(jiekoudizhi, suoyin, rucanshili)
    payload = {}
    headers = {"appkey": str(appkey)}
    response = requests.request("GET", url, headers=headers, data=payload)
    return response.text


def auto_interface_info_check(interface_name):
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
    click('//*[@id="nav-side"]/li[8]/ul/li[2]')  # 点击数据服务维护
    switch('//*[@id="frame"]')  # 切换frame
    write('//*[@id="search_resource_title_code"]', interface_name)  # 填入资源名称
    click('//*[@id="search_resource_status"]/option[5]')  # 点击已发布
    click('//*[@id="search_resource"]')  # 点击查询
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')  # 获取内容

    def match():
        if "表中无数据存在" in x:
            return 0
        for i in range(1, x.count("<tr") + 1):
            # 匹配接口名
            if interface_name == get_text(
                '//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)
            ):
                return i
        return 0

    n = match()
    if n:
        click(
            '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[1]'.format(
                n
            )
        )
        sleep()
        click('//*[@id="tab_1_1"]/div[1]/ul/li[2]/a')  # 点击数据信息服务
        re = get_text(
            '//*[@id="tab_dataService_2"]/div/div[2]/div/table/tbody/tr/td[1]'
        )  # 获取参数名
        return "已发布", re
    else:
        driver.get("http://10.64.202.213/resource")
        driver.switch_to.default_content()  # 切换主页面
        click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
        click('//*[@id="nav-side"]/li[8]/ul/li[2]')  # 点击数据服务维护
        switch('//*[@id="frame"]')  # 切换frame
        write('//*[@id="search_resource_title_code"]', interface_name)  # 填入资源名称
        click('//*[@id="search_resource_status"]/option[8]')  # 点击待测试
        click('//*[@id="search_resource"]')  # 点击查询
        sleep()
        x = get_html('//*[@id="list-table"]/tbody')  # 获取内容

        def match():
            if "表中无数据存在" in x:
                return 0
            for i in range(1, x.count("<tr") + 1):
                # 匹配接口名
                if interface_name == get_text(
                    '//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)
                ):
                    return i
            return 0

        n = match()
        if n:
            click(
                '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[1]'.format(
                    n
                )
            )
            sleep()
            click('//*[@id="tab_1_1"]/div[1]/ul/li[2]/a')  # 点击数据信息服务
            re = get_text(
                '//*[@id="tab_dataService_2"]/div/div[2]/div/table/tbody/tr/td[1]'
            )  # 获取参数名
            return "已发布", re
        else:
            return "找不到接口", ""


def interface_info_check():
    file_name = "资源审核未通过.xlsx"

    def match():
        x = get_html('//*[@id="list-table"]/tbody')  # 获取内容
        if "表中无数据存在" in x:
            return 0
        for i in range(1, x.count("<tr") + 1):
            # 匹配接口名
            if interface_name == get_text(
                '//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)
            ):
                return i
        return 0

    sleep()
    sleep()
    driver.get("http://10.64.202.213/resource")
    sleep()
    sleep()
    driver.switch_to.default_content()  # 切换主页面
    sleep()
    sleep()
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[8]")  # 点击数据服务
    sleep()
    click('//*[@id="nav-side"]/li[8]/ul/li[2]')  # 点击数据服务维护
    sleep()
    switch('//*[@id="frame"]')  # 切换frame
    df = pd.read_excel(file_name, converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "接口状态"] == "已发布":
            continue
        print(i)
        interface_name = df.loc[i, "接口"]
        write('//*[@id="search_resource_title_code"]', interface_name)  # 填入资源名称
        click('//*[@id="search_resource"]')  # 点击查询
        WebDriverWait(driver, 10).until(
            EC.presence_of_element_located((By.XPATH, '//*[@id="list-table"]/tbody'))
        )
        sleep()
        n = match()
        if n:
            df.loc[i, "接口状态"] = get_text(
                '//*[@id="list-table"]/tbody/tr[{}]/td[7]'.format(n)
            )
        else:
            df.loc[i, "接口状态"] = "接口不存在"
        print(df.loc[i, "接口状态"])
    df.to_excel(file_name.replace(".xlsx", "_接口状态结果.xlsx"))


def auto_xinyonghuhuanyuan(dt_name):
    def match():
        x = get_html('//*[@id="list-table"]/tbody')
        if "表中无数据存在" in x:
            return 0
        for j in range(x.count("<tr")):
            if (
                get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(j + 1))
                == dt_name
            ):
                return j + 1
        return 0

    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[4]/a")  ## 点击‘资源维护’
    ## 切换iframe
    switch('//*[@id="frame"]')
    ## 输入查询内容
    write("html/body/div[1]/section/div/form/div[1]/input", dt_name)
    ## 点击库表
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")
    ## 点击查询
    click("/html/body/div[1]/section/div/form/div[6]/button[1]")
    sleep()
    n = match()
    if not n:
        dt_name = dt_name + "新"
        n = match()
    if n:
        click(
            '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[2]'.format(
                n
            )
        )  # 点击变更
        sleep()
        click('//*[@id="steps-uid-0"]/div[3]/ul/li[2]/a')  # 点击下一步
        sleep()
        click('//*[@id="steps-uid-0-p-1"]/div/div[1]/div/div/span/button')  # 点击换源
        sleep()
        write('//*[@id="changeDatasourceKeyword"]', "信用库")
        sleep()
        click('//*[@id="changeDatasourceSearch"]')  # 点击查询
        sleep()
        click('//*[@id="changeDatasourceTable"]/tbody/tr[2]/td[1]/input')  # 选择第二个
        sleep()
        click('//*[@id="changeDatasourceConfirm"]')  # 点击确定
        sleep()
        click('//*[@id="steps-uid-0"]/div[3]/ul/li[4]/a')  # 点击保存并提交
        driver.switch_to.default_content()  # 转到主页面
        sleep()
        click("/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/a")  # 点击资源审核
        click(
            "/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/ul/li[1]/a"
        )  # 点击资源注册审核
        switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 转到frame
        write(
            "/html/body/div[1]/section/div/form/div[1]/input", dt_name
        )  # 写入库表名称
        # click('/html/body/div[1]/section/div/form/div[4]/select') #点击资源类型
        click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")  # 点击库表
        click("/html/body/div[1]/section/div/form/div[5]/button[1]")  # 点击查询
        sleep()
        click(
            "/html/body/div[1]/section/div/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a"
        )  # 点击审核
        sleep()
        click(
            "/html/body/div/section/div/div/div[2]/div[1]/form/div/div/button[1]"
        )  # 点击审核通过
        click("/html/body/div[3]/div[3]/a[1]")  # 点击确定
        sleep()
        sleep()
        driver.switch_to.default_content()  # 切换主页面
        click("/html/body/div[3]/div[1]/div/div[2]/ul/li[6]/a")  # 点击资源发布
        switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 切换frame
        write(
            "/html/body/div[1]/section/div/form/div[1]/input", dt_name
        )  # 输入资源名称
        click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")  # 点击库表
        click('//button[@id="search_resource"]')  # 点击查询
        sleep()
        click(
            "/html/body/div[1]/section/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a"
        )  # 点击发布
        driver.switch_to.default_content()  # 切换主页面
        click('//a[text()="确定"]')  # 点击确定


def xinyonghuhuanyuan():
    df = pd.read_excel("接口第一版-测试未通过.xlsx", converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "页面测试是否通过"] != "数据库填报错误":
            continue
        if df.loc[i, "数据库名"] != "信用库":
            continue
        print(i, df.loc[i, "接口"])
        try:
            auto_xinyonghuhuanyuan(df.loc[i, "库表"])
            df.loc[i, "库表是否存在"] = "库表已更新"
        except:
            continue
    df.to_excel("接口第一版-测试未通过qqqq.xlsx")


def auto_shenhetongguo():
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 转到主页面
    sleep()
    sleep()
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/a")  # 点击资源审核
    sleep()
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/ul/li[1]/a")  # 点击资源注册审核
    sleep()
    switch("/html/body/div[3]/div[2]/div/div[3]/iframe")  # 转到frame
    while True:
        x = get_text('//*[@id="list-table"]/tbody/tr[1]/td[5]')
        if not x.startswith("2023-06-16"):
            break
        click(
            '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[1]/td/a'
        )  # 点击审核
        sleep()
        click('//*[@id="submitDIV"]/div/button[1]')  # 点击审核通过
        sleep()
        click("/html/body/div[3]/div[3]/a[1]")  # 点击确定
        # //*[@id="layui-layer1"]/div[3]/a[1]
        sleep()


def run():
    # 登录
    login_inquiry()
    df = pd.read_excel("全部数据.xlsx", converters={"appkey": str, "入参示例": str})
    # 读取数据
    for i in df.index:
        try:
            if df.loc[i, "接口是否上报"] == "完成":
                continue
            if df.loc[i, "接口是否上报"] == "跳过":
                continue
            if (not pd.isnull(df.loc[i, "接口是否上报"])) and "上报成功！" in df.loc[
                i, "接口是否上报"
            ]:
                continue
            print("序号", i, df.loc[i, "接口"])
            # 接口注册
            print("接口注册……", end=" ")
            if df.loc[i, "接口是否存在"] == "接口已存在":
                print("接口已存在")
            else:
                if not exist_interface(df.loc[i, "接口"]):
                    re = auto_interface_register(
                        "市政务大数据平台",
                        df.loc[i, "目录"],
                        df.loc[i, "库表"],
                        df.loc[i, "表名"],
                        df.loc[i, "索引"],
                        df.loc[i, "接口"],
                        df.loc[i, "接口地址英文后缀"],
                    )
                    df.loc[i, "接口英文名"] = re
                    df.loc[i, "接口是否存在"] = "接口已存在"
                else:
                    df.loc[i, "接口是否存在"] = "接口已存在"
                print("接口已存在")
            # 接口测试
            print("接口页面测试……", end=" ")
            if df.loc[i, "页面测试是否通过"] == "测试通过":
                print("测试通过")
            else:
                re = auto_test(df.loc[i, "接口"], df.loc[i, "入参示例"])
                if re[0] == "测试不通过":
                    print(re[0])
                    print("入参示例：", df.loc[i, "入参示例"])
                    print(re[1])
                    continue
                elif re[0] == "待测试状态中无此接口":
                    print("待测试状态中无此接口")
                    continue
                elif re[0] == "测试通过":
                    print("测试通过")
                    df.loc[i, "页面测试是否通过"] = re[0]
                    df.loc[i, "页面测试输出"] = re[1]
                    print(re[1])
                elif re[0] == "跳过":
                    df.loc[i, "接口是否上报"] = "跳过"
                    continue
            # 资源申请与审核
            print("资源申请与审核……", end=" ")
            if df.loc[i, "资源审核是否通过"] == "资源审核已通过":
                print("资源审核已通过")
            else:
                if pd.isnull(df.loc[i, "接口"]) or df.loc[i, "接口"] == "":
                    df.loc[i, "资源审核是否通过"] = "接口为空"
                    continue
                result = auto_resource_check(df.loc[i, "接口"])
                df.loc[i, "appkey"] = result[0]
                df.loc[i, "接口地址"] = result[1]
                df.loc[i, "资源审核是否通过"] = result[2]
                if result[2] == "接口尚未发布":
                    print("接口尚未发布")
                    quit()
                else:
                    print(result[2])
            # 接口上报
            print("接口上报……", end=" ")
            if df.loc[i, "资源审核是否通过"] == "资源审核已通过" and pd.isnull(
                df.loc[i, "页面测试输出"]
            ):
                df.loc[i, "页面测试输出"] = get_chucan(
                    df.loc[i, "接口地址"],
                    df.loc[i, "索引"],
                    df.loc[i, "入参示例"],
                    df.loc[i, "appkey"],
                )
            if df.loc[i, "接口是否上报"] == "完成":
                print("上报完成")
            else:
                re = auto_submit(
                    df.loc[i, "归属部门"],
                    df.loc[i, "接口"],
                    df.loc[i, "页面测试输出"],
                    df.loc[i, "索引"],
                    df.loc[i, "入参示例"],
                    df.loc[i, "接口地址"],
                    df.loc[i, "表名"],
                    df.loc[i, "索引说明"],
                    df.loc[i, "出参字段描述"],
                )
                df.loc[i, "接口是否上报"] = re
                print("上报完成")
        except Exception as e:
            print(e)
            df.to_excel("全部数据.xlsx")
            # quit()
            continue
    df.to_excel("全部数据.xlsx")


def auto_get_database_info(database_name):
    """
    根据库表名来获取数据库的真实地址
    用于上报过程中,字段描述和返回值不匹配的更正
    """
    driver.get("http://10.64.202.213/resource")
    driver.switch_to.default_content()  # 切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[4]/a")  ## 点击‘资源维护’
    ## 切换iframe
    switch('//*[@id="frame"]')
    ## 输入查询内容
    write("html/body/div[1]/section/div/form/div[1]/input", database_name)
    ## 点击库表
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")
    ## 点击查询
    click("/html/body/div[1]/section/div/form/div[6]/button[1]")
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')

    def match():
        if "表中无数据存在" in x:
            return 0
        for j in range(x.count("<tr")):
            if (
                get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(j + 1))
                == database_name
            ):
                return j + 1
        return 0

    n = match()
    if n:
        click(
            '//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[1]'.format(
                n
            )
        )  # 点击查看
        sleep()
        shujubiaomingcheng = get_html(
            '//*[@id="tab_table_detail"]/div[1]/div/div[1]'
        ).strip()
        shujubiaomingcheng = shujubiaomingcheng[shujubiaomingcheng.find("：") + 1 :][
            :-6
        ].strip()
        print(shujubiaomingcheng)
        shujukumingcheng = get_html(
            '//*[@id="tab_table_detail"]/div[1]/div/div[2]'
        ).strip()
        shujukumingcheng = shujukumingcheng[shujukumingcheng.find("：") + 1 :][
            :-6
        ].strip()
        shujukudizhi = get_html('//*[@id="tab_table_detail"]/div[1]/div/div[3]').strip()
        shujukudizhi = shujukudizhi[shujukudizhi.find("：") + 1 :][:-6].strip()
        return shujukudizhi + "/" + shujukumingcheng, shujubiaomingcheng


def get_database_info():
    file_name = "4代码测试通过294.xlsx"
    df = pd.read_excel(file_name, converters={"appkey": str})
    for i in df.index:
        if df.loc[i, "接口是否上报"] != "数据内容不匹配":
            continue
        print(i, df.loc[i, "接口"])
        try:
            re = auto_get_database_info(df.loc[i, "库表"])
            df.loc[i, "数据库"] = re[0]
            df.loc[i, "表名"] = re[1]
            print(re)
            quit()
        except:
            continue
    df.to_excel(file_name.replace(".xlsx", "_数据库内容更新.xlsx"))


if __name__ == "__main__":
    driver = webdriver.Chrome()
    login_inquiry()
    # interface_info_check()
    # interface()
    # dt()
    # web_test()
    # check()
    submit()
    # get_database_info()
    driver.quit()
